package pro.syj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import pro.syj.bean.Score;
import pro.syj.db.DBassist;

public class ScoreDao {
	/*
	 * 查询某一学生的所有课程成绩
	 * param1 sid
	 * return List<Score>
	 */
	public ArrayList<Score> selectAll(String sid){
		
		ArrayList<Score> scorelist = new ArrayList<>();
		String sql = "select * from score where sid = ?";
		
		try {
			DBassist dba = new DBassist();
			//已封装，查询完成自动释放资源
			ResultSet rss = dba.query(sql,sid);  
			while(rss != null && rss.next()){
				Score score = new Score();
				score.setCid(rss.getString(1));
				score.setCname(rss.getString(2));
				score.setSid(rss.getString(3));
				score.setSname(rss.getString(4));
				score.setScore1(rss.getDouble(5));
				score.setScore2(rss.getDouble(6));
				score.setScore(rss.getDouble(7));
				scorelist.add(score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scorelist;
	}
	
	public ArrayList<Score> selectAll2(String cid){
		
		ArrayList<Score> scorelist = new ArrayList<>();
		String sql = "select * from score where cid = ?";
		
		try {
			DBassist dba = new DBassist();
			//已封装，查询完成自动释放资源
			ResultSet rss = dba.query(sql,cid);  
			while(rss != null && rss.next()){
				Score score = new Score();
				score.setCid(rss.getString(1));
				score.setCname(rss.getString(2));
				score.setSid(rss.getString(3));
				score.setSname(rss.getString(4));
				score.setScore1(rss.getDouble(5));
				score.setScore2(rss.getDouble(6));
				score.setScore(rss.getDouble(7));
				scorelist.add(score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scorelist;
	}
	
	/*
	 * 查询某一学生的某一门课程成绩
	 * param1 sid
	 * param2 cid
	 * return ArrayList<Score> 
	 */
	public ArrayList<Score> selectOne(String sid,String cid){
		
		ArrayList<Score> scorelist = new ArrayList<>();
		String sql = "select * from score where sid = ? and cid = ?";
		
		try{
			DBassist dba = new DBassist();
			ResultSet rss = dba.query(sql, sid, cid);
			if(rss != null && rss.next()){
				Score score = new Score();
				score.setCid(rss.getString(1));
				score.setCname(rss.getString(2));
				score.setSid(rss.getString(3));
				score.setSname(rss.getString(4));
				score.setScore1(rss.getDouble(5));
				score.setScore2(rss.getDouble(6));
				score.setScore(rss.getDouble(7));
				scorelist.add(score);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scorelist;
	}
	
	public Score selectOne2(String sid,String cid){
		
		Score score = new Score();
		String sql = "select * from score where sid = ? and cid = ?";
		
		try{
			DBassist dba = new DBassist();
			ResultSet rss = dba.query(sql, sid, cid);
			if(rss != null && rss.next()){
				score.setCid(rss.getString(1));
				score.setCname(rss.getString(2));
				score.setSid(rss.getString(3));
				score.setSname(rss.getString(4));
				score.setScore1(rss.getDouble(5));
				score.setScore2(rss.getDouble(6));
				score.setScore(rss.getDouble(7));
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return score;
	}
	
	/*
	 * 查询所有学生的所有课程成绩
	 * return ArrayList<Score>
	 */
	public ArrayList<Score> selectAllScore(){
			
			ArrayList<Score> scorelist = new ArrayList<>();
			String sql = "select * from score ";
			
			try {
				DBassist dba = new DBassist();
				//已封装，查询完成自动释放资源
				ResultSet rss = dba.query(sql);  
				while(rss != null && rss.next()){
					Score score = new Score();
					score.setCid(rss.getString(1));
					score.setCname(rss.getString(2));
					score.setSid(rss.getString(3));
					score.setSname(rss.getString(4));
					score.setScore1(rss.getDouble(5));
					score.setScore2(rss.getDouble(6));
					score.setScore(rss.getDouble(7));
					scorelist.add(score);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return scorelist;
		}
	
	/*
	 * 增加（录入）一门学生课程的成绩
	 * param cid，cname，sid，sname，score1，score2，score
	 * return int
	 */
	public int addScore(String cid,String cname,String sid,String sname,double score1,double score2){
		
		String sql = "insert into score value(?,?,?,?,?,?,?)";
		int flag = -1;
		double percent1 = getPercent1(cid);
		double percent2 = getPercent2(cid);
		double score = score1*percent1 + score2*percent2;
		if(percent1 == -1 || percent2 == -1){
			return -1;
		}
		
		try{
			DBassist dba = new DBassist();
			flag = dba.update(sql, cid,cname,sid,sname,score1,score2,score);
			return flag;
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/*
	 * 删除一门学生课程的成绩
	 * param cid，sid
	 * return int
	 */
	public int deleteScore(String cid,String sid){
		
		String sql = "delete from score where cid = ? and sid = ?";
		int flag = -1;
		
		try{
			DBassist dba = new DBassist();
			flag = dba.update(sql, cid,sid);
			return flag;
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	
	/*
	 * 获取这门课程的平时成绩占比
	 * param cid
	 * return double
	 */
	public double getPercent1(String cid){
		
		String sql = "select percent1 from course where cid = ?";
		double percent1 = 0;
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql, cid);
			if(rss != null && rss.next()){
				percent1 = rss.getDouble(1);
			}else{
				percent1 = -1;
			}
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return percent1; 
	}
	
	/*
	 * 获取这门课程的考试成绩占比
	 * param cid
	 * return double
	 */
	public double getPercent2(String cid){
		
		String sql = "select percent2 from course where cid = ?";
		double percent2 = 0;
		
		try{
			DBassist dbs = new DBassist();
			ResultSet rss = dbs.query(sql, cid);
			if(rss != null && rss.next()){
				percent2 = rss.getDouble(1);
			}else{
				percent2 = -1;
			}
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return percent2; 
	}
	
	/*
	 * 修改一门学生课程的成绩
	 * param cid，sid,score1，score2
	 * return int
	 */
	public int updateScore(String cid,String sid,double score1,double score2){
		
		double percent1 = getPercent1(cid);
		double percent2 = getPercent2(cid);
		double score = score1*percent1 + score2*percent2;
		if(percent1 == -1 || percent2 == -1){
			return -1;
		}
		
		
		String sql1 = "update score set score1 = ? where cid = ? and sid = ?";
		String sql2 = "update score set score2 = ? where cid = ? and sid = ?";
		String sql3 = "update score set score = ? where cid = ? and sid = ?";
		
		try{
			DBassist dbs = new DBassist();
			dbs.update(sql1, score1,cid,sid);
			dbs.update(sql2, score2,cid,sid);
			dbs.update(sql3, score,cid,sid);
			return 1;
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return -1;
	}
}


















